CREATE PROCEDURE [dbo].[EventLogs_Search]
	@agent_firstname varchar(100),
	@agent_lastname varchar(100),
	@supervisor_firstname varchar(100),
	@supervisor_lastname varchar(100),
	@from datetime,
	@to datetime,
	@customer_firstname varchar(35),
	@customer_lastname varchar(35),
	@customer_uri varchar(100),
	@hw_party_id bigint,
	@hw_applicant_id integer,
	-- added in classificationid and duration in minutes
    @classification_id int, 
    @duration_from_min int, 
    @duration_to_min int
AS

--Parameter Sniffing Code
DECLARE @Myagent_firstname varchar(100),
	@Myagent_lastname varchar(100),
	@Mysupervisor_firstname varchar(100),
	@Mysupervisor_lastname varchar(100),
	@Myfrom datetime,
	@Myto datetime,
	@Mycustomer_firstname varchar(35),
	@Mycustomer_lastname varchar(35),
	@Mycustomer_uri varchar(100),
	@Myhw_party_id bigint,
	@Myhw_applicant_id INTEGER
	, @Myclassification_id int
	, @Myduration_from_sec int
	, @Myduration_to_sec int
	
SELECT 
	@Myfrom = CASE WHEN @from IS NULL THEN '2011-08-22' ELSE @from END,
	@Myto = CASE WHEN @to IS NULL THEN GETDATE() ELSE @to END,
	@Myagent_firstname = REPLACE(CASE WHEN @agent_firstname IS NULL THEN '%' ELSE '%' + RTRIM(@agent_firstname) + '%' END, '%%', '%'),
	@Myagent_lastname = REPLACE(CASE WHEN @agent_lastname IS NULL THEN '%' ELSE '%' + RTRIM(@agent_lastname) + '%' END, '%%', '%'),
	@Mysupervisor_firstname = REPLACE(CASE WHEN @supervisor_firstname IS NULL THEN '%' ELSE '%' + RTRIM(@supervisor_firstname) + '%' END, '%%', '%'),
	@Mysupervisor_lastname = REPLACE(CASE WHEN @supervisor_lastname IS NULL THEN '%' ELSE '%' + RTRIM(@supervisor_lastname) + '%' END, '%%', '%'),
	@Mycustomer_firstname = REPLACE(CASE WHEN @customer_firstname IS NULL THEN '%' ELSE '%' + RTRIM(@customer_firstname) + '%' END, '%%', '%'),
	@Mycustomer_lastname = REPLACE(CASE WHEN @customer_lastname IS NULL THEN '%' ELSE '%' + RTRIM(@customer_lastname) + '%' END, '%%', '%'),
	@Mycustomer_uri = REPLACE(CASE WHEN @customer_uri IS NULL THEN '%' ELSE '%' + RTRIM(@customer_uri) + '%' END, '%%', '%'),
	@Myhw_party_id = ISNULL(@hw_party_id, 0),
	@Myhw_applicant_id = ISNULL(@hw_applicant_id, 0),
	@Myclassification_id = ISNULL(@classification_id, 0),
	@Myduration_from_sec = ISNULL(@duration_from_min, 0) * 60,
	@Myduration_to_sec = ISNULL(@duration_to_min, 300) * 60
	


SET NOCOUNT ON
	
/* 
	JKH 2011-09-13
	added to restrict views of calls that can be listened to 


*/
-- get the system user (no running as DBO's here!)
DECLARE @sysuser VARCHAR(100)

SELECT @sysuser =  dbo.GetCurrentUser()


-- create a temporary table to hold the results
DECLARE @tmpPermittedAgents TABLE (logonname VARCHAR(100), 
	GivenName VARCHAR(100),
	Surname VARCHAR(100),
	Name VARCHAR(100), 
	Department VARCHAR(100), 
	OBJECT_GUID UNIQUEIDENTIFIER)
	

-- run stored proc to get users into temp table
-- will use an inner join to make sure that stored proc will only return calls
-- for people that report to the current user (somewhere in the chain)
INSERT INTO @tmpPermittedAgents EXEC dbo.GetPermittedAgents @sysuser

-- delete from the permitted agents where not permitted to listen to call
DELETE FROM @tmpPermittedAgents WHERE NOT (GivenName LIKE @Myagent_firstname AND Surname LIKE @Myagent_lastname)

-- create a temporary table to hold the calls
DECLARE @tmpCalls TABLE (CONVERSATION_GUID UNIQUEIDENTIFIER,
	StartTime DATETIME,
	EndTime DATETIME,
	CallID INT,
	EventCode INT,
	EndCode INT,
	AgentGUID UNIQUEIDENTIFIER,
	Agent_First_Name VARCHAR(100),
	Agent_Last_Name VARCHAR(100),
	HW_Applicant_ID INT,
	HW_Party_ID INT,
	First_Name VARCHAR(100),
	Last_Name VARCHAR(100),
	Duration INT
	
	)


-- insert all calls where a permitted agent is involved
INSERT INTO @tmpCalls (CONVERSATION_GUID, AgentGUID, Agent_First_Name, Agent_Last_Name)
SELECT DISTINCT CONVERSATION_GUID, AGENT_AD_GUID, a.GivenName, a.Surname 
FROM EventLogs WITH (NOLOCK)
	INNER JOIN @tmpPermittedAgents a ON EventLogs.AGENT_AD_GUID = a.OBJECT_GUID
	WHERE OCCURRED BETWEEN @MyFrom AND @Myto
		GROUP BY CONVERSATION_GUID, AGENT_AD_GUID, a.GivenName, a.Surname
		
-- update the call details with start time, end time and duration
UPDATE t SET EventCode = c.EventCode, StartTime = c.StartTime, EndTime = c.EndTime, Duration = DATEDIFF(SS, c.StartTime, c.EndTime)
FROM @tmpCalls t INNER JOIN
	(SELECT CONVERSATION_GUID, MIN(EVENT_CODE) AS EventCode, MIN(OCCURRED) AS StartTime, MAX(OCCURRED) AS EndTime FROM EventLogs
		WHERE OCCURRED BETWEEN @MyFrom AND @Myto
		GROUP BY CONVERSATION_GUID) c ON t.CONVERSATION_GUID = c.CONVERSATION_GUID

/*
	set the party or applicant id into the temp table 
*/

UPDATE t SET HW_Party_ID = l.HW_PARTY_ID
FROM @tmpCalls t
	INNER JOIN EventLogs l ON t.CONVERSATION_GUID = l.CONVERSATION_GUID

UPDATE t SET HW_Applicant_ID = l.HW_PARTY_ID
FROM @tmpCalls t
	INNER JOIN EventLogs l ON t.CONVERSATION_GUID = l.CONVERSATION_GUID

-- update the customer name using the partyid
UPDATE t SET First_Name = p.FirstName, Last_Name = p.Surname
FROM @tmpCalls t
	INNER JOIN [Socrates].[Socrates].[dbo].[Party] p ON t.HW_Party_ID = p.PartyID
WHERE t.HW_Party_ID IS NOT NULL	
	
-- if the customer name is blank then update with applicant name
UPDATE t SET First_Name = p.FirstName, Last_Name = p.Surname
FROM @tmpCalls t
	INNER JOIN [Socrates].[Socrates].[dbo].[app_Applicant] p ON t.HW_Applicant_ID = p.ApplicantID
WHERE t.HW_Applicant_ID IS NOT NULL	AND t.First_Name IS NULL AND t.Last_Name IS NULL

-- final select and filter on input parameters

SELECT ISNULL(uri.CUSTOMER_URI, '') AS CUSTOMER_URI
	, t.StartTime AS OCCURRED
	, t.CONVERSATION_GUID
	, t.EventCode AS EVENT_CODE
	, t.Agent_First_Name AS AGENT_FIRST_NAME
	, t.Agent_Last_Name AS AGENT_LAST_NAME
	, asp.FIRST_NAME AS SUPERVISOR_FIRST_NAME
	, asp.LAST_NAME AS SUPERVISOR_LAST_NAME
	, t.HW_Applicant_ID AS HW_APPLICANT_ID
	, t.HW_Party_ID AS HW_PARTY_ID
	, t.First_Name AS FIRST_NAME
	, t.Last_Name AS LAST_NAME
	, cla.NAME AS CONVERSATION_CLASS_NAME
	, t.Duration AS DURATION
FROM @tmpCalls t
	LEFT JOIN (SELECT DISTINCT CUSTOMER_URI, CONVERSATION_GUID FROM EventLogs WITH (NOLOCK) WHERE CUSTOMER_URI IS NOT NULL) uri
		ON t.CONVERSATION_GUID = uri.CONVERSATION_GUID
	LEFT JOIN (SELECT DISTINCT SUPERVISOR_AD_GUID, CONVERSATION_GUID 
		FROM EventLogs WITH (NOLOCK) WHERE SUPERVISOR_AD_GUID IS NOT NULL) sp
		ON t.CONVERSATION_GUID = sp.CONVERSATION_GUID
	LEFT JOIN ADUsers asp WITH (NOLOCK) ON sp.SUPERVISOR_AD_GUID = asp.OBJECT_GUID
	LEFT JOIN (SELECT DISTINCT CONVERSATION_CLASS_ID, CONVERSATION_GUID FROM EventLogs WITH (NOLOCK) WHERE CONVERSATION_CLASS_ID IS NOT NULL) cl
		ON t.CONVERSATION_GUID = cl.CONVERSATION_GUID
	LEFT JOIN Classifications cla WITH (NOLOCK) ON cl.CONVERSATION_CLASS_ID = cla.ID		
WHERE
	ISNULL(asp.FIRST_NAME, '') LIKE @Mysupervisor_firstname
	AND ISNULL(asp.LAST_NAME, '') LIKE @Mysupervisor_lastname
	AND ISNULL(uri.CUSTOMER_URI, '') LIKE @Mycustomer_uri
	AND ISNULL(t.HW_PARTY_ID, 0) =  CASE WHEN @Myhw_party_id = 0 THEN ISNULL(t.HW_PARTY_ID, 0) ELSE @Myhw_party_id END
	AND ISNULL(t.HW_APPLICANT_ID, 0) = CASE WHEN @Myhw_applicant_id = 0 THEN ISNULL(t.HW_APPLICANT_ID, 0) ELSE @Myhw_applicant_id END
	AND ((ISNULL(t.First_Name, '') LIKE @Mycustomer_firstname) OR (ISNULL(t.First_Name, '') LIKE @Mycustomer_firstname))
	AND ((ISNULL(t.Last_Name, '') LIKE @Mycustomer_lastname) OR (ISNULL(t.Last_Name, '') LIKE @Mycustomer_lastname))
	AND ISNULL(cl.CONVERSATION_CLASS_ID, 0) = CASE WHEN @Myclassification_id = 0 
		THEN ISNULL(cl.CONVERSATION_CLASS_ID, 0)
		ELSE @Myclassification_id END
	AND t.Duration between @Myduration_from_sec and @Myduration_to_sec

ORDER BY StartTime

GO

USE [QRD]
GO

/****** Object:  StoredProcedure [dbo].[EventLogs_GetConversationEvents]    Script Date: 05/25/2012 14:59:04 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventLogs_GetConversationEvents]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[EventLogs_GetConversationEvents]
GO

USE [QRD]
GO

/****** Object:  StoredProcedure [dbo].[EventLogs_GetConversationEvents]    Script Date: 05/25/2012 14:59:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[EventLogs_GetConversationEvents]
	@conversation_guid uniqueidentifier
AS
BEGIN

	SELECT 
		EventLogs.CONVERSATION_GUID,
		EventLogs.OCCURRED,
		EventLogs.EVENT_CODE,
		UsersAgents.FIRST_NAME as AGENT_FIRST_NAME,
		UsersAgents.LAST_NAME as AGENT_LAST_NAME,
		UsersSupervisors.FIRST_NAME as SUPERVISOR_FIRST_NAME,
		UsersSupervisors.LAST_NAME as SUPERVISOR_LAST_NAME,	
		EventLogs.CUSTOMER_URI,
		EventLogs.HW_PARTY_ID,
		EventLogs.HW_APPLICANT_ID,
		Socrates_Party.FirstName as FIRST_NAME,
		Socrates_Party.Surname as LAST_NAME,
		Classifications.NAME as CONVERSATION_CLASS_NAME		
	FROM
		[dbo].[EventLogs] EventLogs
	LEFT JOIN
			[dbo].[ADUsers] UsersAgents ON UsersAgents.OBJECT_GUID = EventLogs.AGENT_AD_GUID
	LEFT JOIN
			[dbo].[ADUsers] UsersSupervisors ON UsersSupervisors.OBJECT_GUID = EventLogs.SUPERVISOR_AD_GUID		
	LEFT JOIN
		[dbo].[Socrates_Party] Socrates_Party on 
			(Socrates_Party.TypeID = 'A' AND Socrates_Party.ID = EventLogs.HW_APPLICANT_ID) or 
			(Socrates_Party.TypeID = 'P' AND Socrates_Party.ID = EventLogs.HW_PARTY_ID)
	LEFT JOIN
			[dbo].[Classifications] Classifications ON Classifications.ID = EventLogs.CONVERSATION_CLASS_ID
	WHERE
		EventLogs.CONVERSATION_GUID = @conversation_guid
	ORDER BY 	
		EventLogs.OCCURRED	
		

	INSERT INTO EventLogListener (Listener, ConversationGUID, ListenTime)
		SELECT dbo.GetCurrentUser(), @conversation_guid, GETDATE()
END


GO

